-- This script builds the demo database SQLTitbits. This is a
-- very simple database with just a few tables with a fairly 
-- limited amount of reason and rhyme.
-- © Erland Sommarskog 2020.
USE tempdb
go
DECLARE @version nvarchar(250)
SELECT @version = convert(nvarchar(250), serverproperty('ProductVersion'))
IF @version IS NULL OR convert(int, substring(@version, 1, charindex('.', @version) - 1)) < 13
BEGIN 
   RAISERROR('This script requires SQL 2016 or higher and will attempt to abort.', 16, 127)
   RAISERROR('Trying to abort the connection now.', 20, 1) WITH LOG
END
GO
IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SQLTitbits')
BEGIN
   ALTER DATABASE SQLTitbits SET SINGLE_USER WITH ROLLBACK IMMEDIATE   
   DROP DATABASE SQLTitbits
END
go
CREATE DATABASE SQLTitbits
go
USE SQLTitbits
go
-- A small table with persons that may be responsible for customers.
CREATE TABLE dbo.CustomerResponsibles (
   CustRespID    smallint     NOT NULL,
   FirstName     nvarchar(20) NOT NULL,
   LastName      nvarchar(30) NOT NULL,
   Location      nvarchar(50) NOT NULL,
   CONSTRAINT pk_CustomerResponsibles PRIMARY KEY(CustRespID)
)
INSERT dbo.CustomerResponsibles (CustRespID, FirstName, LastName, Location)
   VALUES(1, N'Jeanette', N'Dupont',    N'Bruxelles'),
         (2, N'Gretchen', N'Paton',     N'Auckland'),
         (3, N'Keith',    N'Wakeman',   N'Newcastle'),
         (4, N'Annie',    N'Clark',     N'Dallas'),
         (5, N'Ana',      N'Márquez',   N'Madrid'),
         (6, N'Tadeuz',   N'Cugowski',  N'Wrocław'),
         (7, N'Sara',     N'Lindberg',  N'Säffle'),
         (8, N'Marcello', N'Graselli',  N'Rijeka'),
         (9, N'Shinzo',   N'Natsumori', N'Tokyo')
    
-- Customers can belong to a category. They are defined here.
-- (This table is not actually used in the main version of the
-- presentation.)
CREATE TABLE dbo.CustomerCategories (
   CategoryCode    varchar(5)   NOT NULL,
   CategoryName    nvarchar(50) NOT NULL,
   CONSTRAINT pk_CustomerCategories PRIMARY KEY (CategoryCode)
)
INSERT dbo.CustomerCategories (CategoryCode, CategoryName)
   VALUES('1',     N'First segment'),
         ('2',     N'Second segment'),
         ('3',     N'Third segment'),
         ('1,2,3', N'Combined segment'),
         ('Au',    N'Gold customers'),
         ('Ag',    N'Silver customers'),
         ('Bro',   N'Bronze customers'),
         ('Fe',    N'Iron customers'),
         ('Al',    N'Aluminum customers'),
         ('Pla',   N'Plastic customers'),
         ('0',     N'Zero-value customers')

-- Here is a Customers table which makes a feeble attempt to be
-- realistic.
CREATE TABLE dbo.Customers (
   CustomerID      int          NOT NULL,
   CustomerName    nvarchar(50) NOT NULL,
   StreetAddr      nvarchar(50) NOT NULL,
   ZipCode         varchar(15)  NOT NULL,
   City            nvarchar(50) NOT NULL,
   Country         nvarchar(30) NOT NULL,
   CustRespID      smallint     NULL,
   CategoryCode    varchar(5)   NULL,
   DeregDate       date         NULL,
   CONSTRAINT pk_Customers PRIMARY KEY (CustomerID),
   CONSTRAINT fk_Customers_CustomerResponsibles FOREIGN KEY (CustRespID)
      REFERENCES dbo.CustomerResponsibles (CustRespID),
   CONSTRAINT fk_Customers_CustomerCategories FOREIGN KEY (CategoryCode)
      REFERENCES dbo.CustomerCategories (CategoryCode),
)

INSERT dbo.Customers (CustomerID, CustomerName, StreetAddr, ZipCode, City, Country,  CustRespID, CategoryCode, DeregDate)
  VALUES(1,  N'Allan Larsson',      N'Roslagsgatan 12',       '11311',     N'Stockholm',    'Sweden',      7,    '0',     '20160212'),
        (2,  N'Charles Bomarbdier', N'22 Farm Street',        '9730',      N'Lumsden',      'New Zealand', 2,    'Au',    NULL),
        (3,  N'Tina Schlumpf',      N'Tornquiststraße 23',    '20259',     N'Hamburg',      'Germany',     1,    NULL,    NULL),
        (4,  N'Gustavo Mazetti',    N'Av San Martin 456',     '89144',     N'Tornquist',    'Argentina',   NULL, 'Ag',    NULL),
        (5,  N'Sunita Kulkarni',    N'Chavhan Rd 14',         '411042',    N'Pune',         'India',       NULL, '1',     NULL),
        (6,  N'Adam Smith',         N'14 Lesser High Street', 'NG7 1AY',   N'Nottingham',   'UK',          3,    '2',     '20190201'),
        (7,  N'Suzy Creamcheese',   N'4321 108th Street SW',  '90123',     N'Glendale, CA', 'USA',         4,    '3',     NULL),
        (8,  N'Hanna Potrzańska',   N'Jarosłavska 49',        '54211',     N'Wrocław',      'Poland',      6,    'Pla',   NULL),
        (9,  N'Michał Przykucki',   N'Pelpińska 23',          '67211',     N'Poznań',       'Poland',      6,    '1,2,3', NULL),
        (10, N'Monica Takeshita',   N'140 Chome-5-8-Befu',    '814-0104',  N'Fukuoka',      'Japan',       9,     NULL,   NULL),
        (11, N'Jomo Migwi',         N'80 Victoria Street',    '12345',     N'Mombasa',      'Kenya',       NULL, 'Bro',   NULL),
        (12, N'Park Il-Sung',       N'92 Yudal-ro',           '65432',     N'Mokpo',        'South Korea', 9,    '1',     NULL),
        (13, N'Jack Frost',         N'865 Doucglas Road',     '91000',     N'Victoria',     'Canada',      4,    '2',     NULL),
        (14, N'Jacques Brel',       N'Rue de Midi 85',        '0435',      N'Bruxelles',    'Belgium',     1,    NULL,    NULL),
        (15, N'Kemal Hasanoğlu',    N'1. Hürriey Cd. 143',    '16160',     N'Bursa',        'Turkey',      NULL, '3',     '20180118'),
        (16, N'Oksana Tymoshenko',  N'Andrevsky Uzviz 14',    '10111',     N'Kyiv',         'Ukraine',     6,    '1',     NULL),
        (17, N'Milan Modrić',       N'Rujevica 19',           '51000',     N'Rijeka',       'Croatia',     6,    '1',     NULL),
        (18, N'Leena Haapaniemi',   N'Kutojantie 10',         '01233',     N'Espoo',        'Finland',     7,    '2',     NULL),
        (19, N'Hosni Al-Sisi',      N'12 Al Farabi',          '00000',     N'Alexandria',   'Egypt',       NULL, '2',     NULL),
        (20, N'Sun Jinping',        N'14 Linli Ave',          '77-77-77',  N'Nanjing',      'China',       NULL, '1',     NULL)

go

-- As the name suggests, don't design tables like this. Particularly,
-- don't store comma-separated lists in a table!
CREATE TABLE BadDesign (id         int          NOT NULL,
                        name       varchar(20)  NOT NULL,
                        valuelist  varchar(MAX) NULL,
                        CONSTRAINT pk_BadDesign PRIMARY KEY(id))
go
INSERT BadDesign (id, name, valuelist)
   VALUES(1, 'Name one',   '1,2,3,4,5,99,102,8,23,1'),
         (2, 'Name two',   '989,234,21355,1235'),
         (3, 'Name three', '98,234,56,91,1435'),
         (4, 'Name four',  NULL)
go


-- This table mimicks a configuration table. Many systems needs a lot of
-- configuration values. A "proper" design of a table like this is one
-- column per configuration option, but that is not tenable, so most
-- people have one row per option. In this version we have different
-- columns depending on the data type for the config option.
CREATE TABLE dbo.ConfigValues (id           int              NOT NULL,
                               name         varchar(50)      NOT NULL,
                               type         char(4)          NOT NULL,
                                  CONSTRAINT ck_DatatypeTbl CHECK (type IN ('int', 'str', 'date', 'dtm', 'guid')),
                               intval       int              NULL,
                               strval       varchar(50)      NULL,
                               dateval      date             NULL,
                               datetimeval  datetime         NULL,
                               guidval      uniqueidentifier NULL,
                               CONSTRAINT pk_DatatypeTbl PRIMARY KEY (id)
)
go
INSERT dbo.ConfigValues (id, name, type, intval)
   VALUES(1, 'An integer value',  'int', 1234)
INSERT dbo.ConfigValues (id, name, type, strval)
   VALUES(2, 'A string value',  'str', 'A string'),
         (3, 'A numeric string', 'str', '1234')
INSERT dbo.ConfigValues (id, name, type, dateval)
   VALUES(4, 'A date value',  'date', '1969-07-21')
INSERT dbo.ConfigValues (id, name, type, datetimeval)
   VALUES(5, 'A datetime value',  'dtm', sysdatetime())
INSERT dbo.ConfigValues (id, name, type, guidval)
   VALUES(6, 'A GUID',  'guid', newid())


go
-- Two transactions table. While they have the same design, the TransactionID
-- are intended to be unrelated. Think Eastern Bank and Western Bank.
-- Used for the demos on UNION.
CREATE TABLE dbo.EastTransactions(TransactionID      int          NOT NULL,
                                  Name               nvarchar(20) NOT NULL,
                                  Qty                int          NOT NULL,
                                  TransDate          date         NOT NULL,
                                  CONSTRAINT pk_EastTransactions PRIMARY KEY (TransactionID)
)

CREATE TABLE dbo.WestTransactions(TransactionID      int          NOT NULL,
                                  Name               nvarchar(20) NOT NULL,
                                  Qty                int          NOT NULL,
                                  TransDate          date         NOT NULL,
                                  CONSTRAINT pk_WestTransactions PRIMARY KEY (TransactionID)
)

INSERT dbo.EastTransactions (TransactionID, Name, Qty, TransDate)
  VALUES(1, 'Adam',  20, '2020-01-15'),
        (2, 'Tina',  10, '2020-01-16'),
        (3, 'Sue',   19, '2020-01-16'),
        (4, 'Adam',  20, '2020-01-19'),
        (5, 'Pat',   12, '2020-01-22')


INSERT dbo.WestTransactions (TransactionID, Name, Qty, TransDate)
  VALUES(1, 'Ben',   12, '2020-01-12'),
        (2, 'Tina',  10, '2020-01-16'),
        (3, 'Adam',  14, '2020-01-19'),
        (4, 'Pat',   12, '2020-01-25'),
        (5, 'Pat',   31, '2020-01-25')
go
-- This function has no ambition more than just being a function for 
-- the sake of it.
CREATE FUNCTION dbo.MyDemoFunc(@x float) RETURNS float AS
BEGIN 
   RETURN sin(radians(@x))
END
